<?php
/**
 * Created by PhpStorm.
 * User: locove
 * Date: 15-4-15
 * Time: 下午3:12
 * 获取上个月每个sku的最长交期，用于采购提成计算
 */
class longlead{
    public static function longleads($minDate, $maxDate){
        $nowDate = date("Y-m-d H:i:s");

        $sql = "SELECT
            UPPER(`warehouseorders`.`sku`) `sku`,                                                       /*大写 SKU*/
            `warehouseorders`.`supplier_id`,                                                            /*供应商ID*/
            `warehouseorders`.`purchaseorders_id`,                                                      /*采购单号*/
            UNIX_TIMESTAMP(`warehouseorders`.`warehouse_date`) `warehouse_date`,                        /*入库时间戳*/
            UNIX_TIMESTAMP(`purchaseorder_details`.details_date) details_date                           /*下单时间*/
        FROM
            `warehouseorders`
                LEFT JOIN `purchaseorder_details` ON
                    `purchaseorder_details`.purchaseorder_id = `warehouseorders`.`purchaseorders_id`    /*采购单主键*/
                AND `purchaseorder_details`.`sku` = `warehouseorders`.`sku`                             /*SKU主键*/
        WHERE
            `warehouseorders`.`purchaseorders_id` <> '0'
        AND `warehouseorders`.`type` = '10'
        AND `warehouseorders`.`warehouse_date` < '{$maxDate}'
         AND `warehouseorders`.`warehouse_date` >= '{$minDate}'
         AND `purchaseorder_details`.`details_date`>='{$minDate}'
         AND `purchaseorder_details`.`details_date`<'{$maxDate}'
         ORDER BY `warehouseorders`.`warehouse_date` DESC";

        while( tool::eachSql($sql, $data) ) {
            foreach($data as &$v) {
                $temp = $v['warehouse_date'] - $v['details_date'];                                                      //时间戳差值
                $iSql = "INSERT INTO `ldt_bases` (
                    `sku`, `su_id`, `pu_id`, `add_time`,
                    `put_sec`, `wat_sec`, `ldt_sec`
                ) SELECT
                    '{$v['sku']}', '{$v['supplier_id']}', '{$v['purchaseorders_id']}', '{$nowDate}',
                    '{$v['details_date']}', '{$v['warehouse_date']}', '{$temp}'
                FROM
                    (SELECT TRUE) `data`
                WHERE
                    NOT EXISTS(
                        SELECT
                            TRUE
                        FROM
                            `ldt_bases`
                        WHERE
                            `sku` = '{$v['sku']}'
                        AND `pu_id` = '{$v['purchaseorders_id']}'
                        LIMIT 1
                    )";

                DB::query(Database::INSERT, $iSql)->execute('newerp');
            }
        }


        $sql = "SELECT
            `sku`
        FROM
            `ldt_bases`
        WHERE
            `add_time` > '{$minDate}'
        GROUP BY
            `sku`";                                                                                             //需统计的sku和供应商

        while( tool::eachSql($sql, $data) ) {
            foreach($data as &$vd) {
                $cSql = "SELECT
                    `ldt_sec`
                FROM
                    `ldt_bases`
                WHERE
                    `sku` = '{$vd['sku']}'
                ORDER BY
                    /*`add_time` DESC,*/ `ldt_sec` DESC
                LIMIT 1";
                $cData = DB::query(Database::SELECT, $cSql)->execute('newerp')->as_array();                             //读取数据库最大时间

                $iSql = "INSERT INTO `ldt_ords` (
                    `id`, `sku`, `su_id`, `ldt_sec`,
                    `add_time`, `up_time`
                ) SELECT
                    `data`.id, '{$vd['sku']}', 0, '{$cData[0]['ldt_sec']}',
                    NOW(), NOW()
                FROM
                    (SELECT TRUE) `temp`
                        LEFT JOIN (SELECT
                                id
                            FROM
                                `ldt_ords`
                            WHERE
                                `sku` = '{$vd['sku']}'

                        ) `data` ON
                            TRUE
                ON DUPLICATE KEY UPDATE
                    `ldt_sec` = '{$cData[0]['ldt_sec']}',
                    `up_time` = NOW()";

                DB::query(Database::INSERT, $iSql)->execute('newerp');
            }
        }
    }

    public static function creatTbl() {
        $sql = "CREATE TABLE IF NOT EXISTS `ldt_bases` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `sku` varchar(50) NOT NULL DEFAULT '',
          `su_id` int(11) NOT NULL DEFAULT '0' COMMENT '供应商ID',
          `pu_id` int(11) NOT NULL DEFAULT '0' COMMENT '采购单号',
          `put_sec` int(11) NOT NULL DEFAULT '0' COMMENT '下单时间戳',
          `wat_sec` int(11) NOT NULL DEFAULT '0' COMMENT '入库时间戳',
          `ldt_sec` int(11) NOT NULL DEFAULT '0' COMMENT '时间戳差值',
          `add_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '记录添加时间',
          PRIMARY KEY (`id`),
          KEY `taskLdImport` (`sku`,`pu_id`) USING BTREE,
          KEY `taskLdtCount` (`add_time`) USING BTREE
        ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='已入库供应商SKU下单入库时间表'";

        DB::query(Database::SELECT, $sql)->execute('newerp');

        $sql = "CREATE TABLE IF NOT EXISTS `ldt_ords` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `sku` varchar(50) NOT NULL DEFAULT '',
          `su_id` int(11) NOT NULL DEFAULT '0',
          `ldt_sec` int(11) NOT NULL DEFAULT '0' COMMENT '交期时间戳',
          `add_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '添加时间',
          `up_time` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '更新时间',
          PRIMARY KEY (`id`),
          KEY `taskLdtCount` (`sku`,`su_id`) USING HASH
        ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='供应商SKU交期表'";

        DB::query(Database::SELECT, $sql)->execute('newerp');
    }

    public static function dropTbl() {
        DB::query(Database::SELECT, "DROP TABLE `ldt_bases`")->execute('newerp');
        DB::query(Database::SELECT, "DROP TABLE `ldt_ords`")->execute('newerp');
    }
}